package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import beans.BacklogViewBean;
import beans.SprintItemBean;
import beans.SprintViewBean;
import beans.UserStoryBean;

import controller.UpdateBacklogPriority;

import com.google.gson.*;

public class SprintViewDao {
	private DataAccess dataAccess = new DataAccess();

	public SprintViewBean getSprintViewInfo(int projectId) throws ClassNotFoundException, SQLException {
		SprintViewBean sprintViewBean = null;
		
		String query = "SELECT S.ID, S.NAME, S.DESCRIPTION, START_DATE, END_DATE,"
				+ " U.ID AS USER_STORY_ID, U.NAME AS USER_STORY_NAME,"
				+ " U.DESCRIPTION AS USER_STORY_DESCRIPTION, U.TYPE, UST.TYPE_NAME, STORY_POINTS"
				+ " FROM SPRINTS S LEFT OUTER JOIN USER_STORIES U ON S.ID = U.SPRINT_ID"
				+ " LEFT OUTER JOIN USER_STORY_TYPE UST ON U.TYPE = UST.ID"
				+ " WHERE S.PROJECT_ID = " + projectId
				+ " ORDER BY START_DATE DESC, PRIORITY";

		ResultSet rset = dataAccess.getSelectResult(query);
		
		sprintViewBean = new SprintViewBean();
		while (rset.next()) {
			SprintItemBean item = new SprintItemBean();
			item.setId(rset.getInt("ID"));
			item.setName(rset.getString("NAME"));
			item.setDescription(rset.getString("DESCRIPTION"));
			item.setStartDate(rset.getString("START_DATE"));
			item.setEndDate(rset.getString("END_DATE"));

			if (rset.getString("USER_STORY_ID") != null) {
				List<UserStoryBean> userStoryList = new ArrayList<UserStoryBean>();
				UserStoryBean userStory = new UserStoryBean();
				
				userStory.setId(rset.getInt("USER_STORY_ID"));
				userStory.setName(rset.getString("USER_STORY_NAME"));
				userStory.setDescription(rset.getString("USER_STORY_DESCRIPTION"));
				userStory.setType(rset.getInt("TYPE"));
				userStory.setTypeName(rset.getString("TYPE_NAME"));
				userStory.setStoryPoints(rset.getInt("STORY_POINTS"));
				if (rset.wasNull()) {
					userStory.setStoryPoints(null);
				}
				
				userStoryList.add(userStory);
				
				item.setUserStories(userStoryList);
			}
			sprintViewBean.addSprintItem(item);
		}
		dataAccess.closeConnection();
		
		return sprintViewBean;		
	}
	
	public int insert(SprintItemBean sprintItem) throws ClassNotFoundException, SQLException {
		int rowsUpdated = 0;
		
		String query = "INSERT INTO SPRINTS (NAME, DESCRIPTION, START_DATE, END_DATE, PROJECT_ID)"
				+ " VALUES (?, ?, ?, ?, ?)";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, sprintItem.getName());
		preparedStatement.setString(2, sprintItem.getDescription());
		preparedStatement.setDate(3, getDate(sprintItem.getStartDate()));
		preparedStatement.setDate(4, getDate(sprintItem.getEndDate()));
		preparedStatement.setInt(5, sprintItem.getProjectId());
		
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				sprintItem.setId(rset.getInt(1));
			}
		}
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	// returns individual sprint data 
	public SprintItemBean getSprintInfo(int sprintId) throws ClassNotFoundException, SQLException {
		SprintItemBean sprintItem = new SprintItemBean();
		
		String query = "SELECT ID, NAME, DESCRIPTION, START_DATE, END_DATE"
				+ " FROM SPRINTS"
				+ " WHERE SPRINTS.ID = " + sprintId;

		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			sprintItem.setId(rset.getInt("ID"));
			sprintItem.setName(rset.getString("NAME"));
			sprintItem.setDescription(rset.getString("DESCRIPTION"));
			sprintItem.setStartDate(rset.getString("START_DATE"));
			sprintItem.setEndDate(rset.getString("END_DATE"));
		}
		dataAccess.closeConnection();

		return sprintItem;
	}

	public int update(SprintItemBean sprintItemBean) throws ClassNotFoundException, SQLException {
		String query = "UPDATE SPRINTS SET NAME = ?, DESCRIPTION = ?, START_DATE = ?, END_DATE = ?"
				+ " WHERE ID = ?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, sprintItemBean.getName());
		preparedStatement.setString(2, sprintItemBean.getDescription());
		preparedStatement.setDate(3, getDate(sprintItemBean.getStartDate()));
		preparedStatement.setDate(4, getDate(sprintItemBean.getEndDate()));
		preparedStatement.setInt(5, sprintItemBean.getId());
		
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();
		
		return rowsUpdated;
	}
	
	public int delete(int id) throws ClassNotFoundException, SQLException {
		String query = "DELETE FROM SPRINTS WHERE ID = ?";
		int rowsUpdated = 0;

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, id);
		
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	private java.sql.Date getDate(String date) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date parsedDate = null;
        
		try {
			parsedDate = format.parse(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
        java.sql.Date sqlDate = new java.sql.Date(parsedDate.getTime());
        
        return sqlDate;
	}
	
	public int cancelSprint(int projectId, int sprintId) throws ClassNotFoundException, SQLException {
		int taskCount = 0;
		int tasksCompleteCount = 0;
		int numberOfUserStoriesMovedToBacklog = 0;
		final int DONE_STATUS = 3;	// TODO use database value
		List<Integer> userStoriesToBacklog = new ArrayList<Integer>();
		List<Integer> sprintBacklog = new ArrayList<Integer>();
		Connection conn = null;

		try {
			conn = dataAccess.getConnection();
			conn.setAutoCommit(false);
			String query = "SELECT US.ID AS USER_STORY_ID, US.NAME AS USER_STORY_NAME,"
					+ " T.ID AS TASK_ID, T.NAME AS TASK_NAME, T.STATUS_ID AS TASK_STATUS"
					+ " FROM USER_STORIES US LEFT OUTER JOIN TASKS T ON US.ID = T.USER_STORY_ID"
					+ " WHERE US.SPRINT_ID = ?"
					+ " ORDER BY USER_STORY_ID";

			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setInt(1, sprintId);
			ResultSet rset = dataAccess.getSelectResult(preparedStatement);
			
			int previousUserStoryId = 0;
			while (rset.next()) {
				int userStoryId = rset.getInt("USER_STORY_ID");
	
				// if new user story, check previous one's tasks now that we have all the data
				if (previousUserStoryId != userStoryId && previousUserStoryId != 0) {
					// check if this user story needs to move to the backlog
					numberOfUserStoriesMovedToBacklog = positionUserStory(
							taskCount,
							tasksCompleteCount,
							numberOfUserStoriesMovedToBacklog,
							userStoriesToBacklog,
							sprintBacklog,
							previousUserStoryId);
	
					// reset for next user story
					taskCount = 0;
					tasksCompleteCount = 0;
				}
				
				if (rset.getInt("TASK_STATUS") == DONE_STATUS) {
					tasksCompleteCount++;
				}
				
				taskCount++;
				previousUserStoryId = userStoryId;
			}
	
			numberOfUserStoriesMovedToBacklog = positionUserStory(
					taskCount,
					tasksCompleteCount,
					numberOfUserStoriesMovedToBacklog,
					userStoriesToBacklog,
					sprintBacklog,
					previousUserStoryId);
	
			moveAndResetPriority(conn, projectId, userStoriesToBacklog, sprintId, sprintBacklog);
			updateEndDate(conn, sprintId);
		} catch (SQLException e ) {
			conn.rollback();
			throw e;
		}
		
		conn.commit();
		conn.setAutoCommit(true);
		dataAccess.closeConnection();
		
		return numberOfUserStoriesMovedToBacklog;
	}

	private int positionUserStory(int taskCount,
			int tasksCompleteCount,
			int numberOfUserStoriesMovedToBacklog,
			List<Integer> userStoriesToBacklog,
			List<Integer> sprintBacklog,
			int previousUserStoryId) {
		
		if (tasksCompleteCount != taskCount) {
			numberOfUserStoriesMovedToBacklog++;
			userStoriesToBacklog.add(previousUserStoryId);
		}
		else {
			sprintBacklog.add(previousUserStoryId);
		}
		
		return numberOfUserStoriesMovedToBacklog;
	}
	
	private int moveUserStoryToBackLog(Connection conn, int userStoryId) throws ClassNotFoundException, SQLException {
		String query = "UPDATE USER_STORIES SET SPRINT_ID = NULL WHERE ID = ?";
		int rowsUpdated = 0;

		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, userStoryId);
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);

		return rowsUpdated;
	}

	private void moveAndResetPriority(Connection conn, int projectId, List<Integer> backlogItems, int sprintId, List<Integer> sprintItems)
					throws ClassNotFoundException, SQLException {
		
		// get actual current backlog user story ids and add to our backlog items list
		UserStoryDao userStoryDao = new UserStoryDao();

		try {
			StringBuffer sb = new StringBuffer();
			// put sprint user stories to move first in the backlog list
			for (int i=0; i < backlogItems.size(); i++) {
				sb.append(backlogItems.get(i) + ",");
			}
						
			// now add the other backlog items
			BacklogViewBean backlogViewBean = userStoryDao.getBacklogViewInfo(projectId);

			JsonElement jelement = new JsonParser().parse(backlogViewBean.getJson());
			JsonObject  jobject = jelement.getAsJsonObject();
			JsonArray jarray = jobject.getAsJsonArray("row");

		    for (int i = 0; i < jarray.size(); i++) {
		    	jobject = jarray.get(i).getAsJsonObject();
		    	if (i + 1 == jarray.size()) {
			    	sb.append(jobject.get("user_story_id").toString());
		    	}
		    	else {
		    		sb.append(jobject.get("user_story_id").toString() + ",");
		    	}
		    }
		    
			for (int i = 0; i < backlogItems.size(); i++) {
				moveUserStoryToBackLog(conn, backlogItems.get(i));
			}
		    UpdateBacklogPriority.prioritizeBacklogItems(conn, sb.toString().replaceAll(",$", ""), 0);	// prioritize backlog

			// prioritize sprint backlog
		    sb.delete(0, sb.length());
		    for (int i = 0; i < sprintItems.size(); i++) {
		    	if (i + 1 == sprintItems.size()) {
			    	sb.append(sprintItems.get(i));
		    	}
		    	else {
		    		sb.append(sprintItems.get(i) + ",");
		    	}
		    }
		    UpdateBacklogPriority.prioritizeBacklogItems(conn, sb.toString(), sprintId);
		    
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	private int updateEndDate(Connection conn, int sprintId) throws ClassNotFoundException, SQLException {
		String query = "UPDATE SPRINTS SET END_DATE = NOW() WHERE ID = ?";
		int rowsUpdated = 0;

		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, sprintId);
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);

		return rowsUpdated;
	}

}
